-- EXEC SP_CREATE_NEW_CUSTOMER 'TEST', 'End User'
CREATE PROC SP_CREATE_NEW_CUSTOMER( @Customer_name VARCHAR(65), @Customer_Type VARCHAR(50) )
AS
BEGIN
	DECLARE @Customer_ID INT
	DECLARE @SHIPPING_ADDRESS_ID INT
	DECLARE @BILLING_ADDRESS_ID INT
	DECLARE @CUSTOMER_TYPE_ID INT
	SELECT @Customer_ID = Customer_ID FROM CUSTOMER WHERE C_NAME = @Customer_name
	IF @Customer_ID IS NULL OR @Customer_ID < 1
	BEGIN
		Insert into Address (Address_1) values('')
		SELECT @SHIPPING_ADDRESS_ID = @@IDENTITY 
		Insert into Address (Address_1) values('')
		SELECT @BILLING_ADDRESS_ID = @@IDENTITY 
		INSERT INTO Customer
           ([C_Name]
           ,[C_Number]
           ,[C_Contact_Person]
           ,[C_Shipping_Method_1]
           ,[C_Shipping_Method_2]
           ,[C_Shipping_method_3]
           ,[C_Act_Number_1]
           ,[C_Act_Number_2]
           ,[C_Act_Number_3]
           ,[C_Region]
           ,[C_Rep_Office]
           ,[C_Sales_Person]
           ,[C_Shipping_Instructions]
           ,[C_Default_Shipping_Address_ID]
           ,[C_Default_Billing_Address_ID]
           ,[C_Cancellation_Window]
           ,[C_Revision_Restriction]
           ,[C_Location_Restriction]
           ,[C_Datecode_Restriction]
           ,[C_Traycode_Restriction]
           ,[C_Least_Shippable_Qty]
           ,[C_Credit_Limit]
           ,[C_Shipping_Restriction]
           ,[C_NoOf_ShippingDays_NotEarlier_Than]
           ,[C_Disabled]
           ,[C_Sales_Rep]
           ,[C_Print_Name]
           ,[C_Supplier_Number]
           ,[C_Contract_Number]
           ,[C_Specification_Number]
           ,[C_Custom_Requirements]
           ,[C_ECN_Change_Notification]
           ,[C_JIT_WINDOW]
           ,[C_Is_Distributor]
           ,[C_IS_CONSIGNEE]
           ,[C_IS_DEFAULT_CONSIGNMENT]
           ,[C_IS_TRACK_INVENTORY]
           ,[C_DIST_REVENUE_TYPE]
           ,[C_PAY_TERMS])
     VALUES
           (@Customer_name, @Customer_name, '','', NULL, NULL,'', NULL,NULL
           ,'','','','',@SHIPPING_ADDRESS_ID,@BILLING_ADDRESS_ID           
           ,0,1,1,1,1,0,0,1,0,1,'','','','','',1,1,0,0,1,1,0,0,'')

		SELECT @Customer_ID = @@IDENTITY 
		SELECT @CUSTOMER_TYPE_ID = C_Type_ID FROM Customer_Types WHERE C_TYPE_NAME = @Customer_Type
		INSERT INTO Customer_Type_Relationship VALUES (@Customer_ID, @CUSTOMER_TYPE_ID )
	END
	ELSE
	BEGIN
		DECLARE @ROW_COUNT INT 
		SELECT @CUSTOMER_TYPE_ID = C_Type_ID FROM Customer_Types WHERE C_TYPE_NAME = @Customer_Type
		PRINT @CUSTOMER_TYPE_ID
		SELECT @ROW_COUNT = COUNT(*) FROM Customer_Type_Relationship WHERE Customer_ID = @Customer_ID AND C_TYPE_ID = @CUSTOMER_TYPE_ID
		PRINT @ROW_COUNT

		IF @ROW_COUNT IS NULL OR @ROW_COUNT <= 0
		BEGIN
			INSERT INTO Customer_Type_Relationship VALUES (@Customer_ID, @CUSTOMER_TYPE_ID )
		END
	END
END